package com.skillworld.webapp.model.tutorial;

import java.util.List;
import java.util.Locale;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.springframework.stereotype.Repository;

import com.skillworld.webapp.model.course.Course;
import com.skillworld.webapp.model.tutorial.Tutorial.Status;
import com.skillworld.webapp.model.util.dao.GenericDaoHibernate;

import es.udc.pojo.modelutil.exceptions.InstanceNotFoundException;


@Repository("TutorialDao")
public class TutorialDaoHibernate extends GenericDaoHibernate<Tutorial,Long> implements TutorialDao
{
    public Criteria buildSearch(List<String> keywords, 
                                Long dept, Integer duration, Locale locale, 
                                List<Status> status, 
                                Long author, List<Long> nonAuthors, 
                                MarkFilter marked, Long notInCourse)
    {
        Criteria q = getSession().createCriteria(Tutorial.class);

        for (String k : keywords)
        {
            Criterion inTitle = Restrictions.ilike("title", k, MatchMode.ANYWHERE);
            Criterion inAbstract = Restrictions.ilike("summary", k, MatchMode.ANYWHERE);
            q.add(Restrictions.or(inTitle, inAbstract));
        }
        
        if (dept != null)
        { q.add(Restrictions.eq("department.departmentId", dept)); }
        
        if (duration != null)
        { q.add(Restrictions.le("duration", duration)); }
        
        if (locale != null)
        { q.add(Restrictions.le("language", locale)); }
        
        if (status != null && !status.isEmpty())
        { q.add(Restrictions.in("status", status)); }
        
        if (author != null)
        { q.add(Restrictions.eq("author.userId", author)); }
        
        if (nonAuthors != null && !nonAuthors.isEmpty())
        { q.add(Restrictions.not(Restrictions.in("author.userId", nonAuthors))); }
        
        if (marked != null)
        {            
            DetachedCriteria markedTutorials = 
                DetachedCriteria.forClass(MarkedTutorial.class)
                    .add(Restrictions.eq("user.userId", marked.getMarkedBy()))
                    .add(Restrictions.eq("mark", marked.getMarkedWith()))
                    .setProjection(Property.forName("tutorial.tutorialId"));    

            q.add(Subqueries.propertyIn("tutorialId", markedTutorials));
        }
        
        if (notInCourse != null)
        {
            DetachedCriteria courseTutorials =
                DetachedCriteria.forClass(Course.class)
                    .add(Restrictions.idEq(notInCourse))
                    .createAlias("lessons", "l")
                    .createAlias("l.tutorials", "t")
                    .setProjection(Property.forName("t.tutorialId"));
            
            q.add(Subqueries.propertyNotIn("tutorialId", courseTutorials));
        }
        
        return q;
    }
    
	@SuppressWarnings("unchecked")
	public List<Tutorial> search(List<String> keywords, 
	                                Long dept, Integer duration, Locale locale, 
	                                List<Status> status, 
	                                Long author, List<Long> nonAuthors, 
	                                MarkFilter marked,
	                                Long notInCourse, 
	                                TutorialOrder order, 
	                                int startIndex, int count) 
	{
	    return(List<Tutorial>)
    		    buildSearch(keywords, 
    		                    dept, duration, locale, status, 
    		                    author, nonAuthors, 
    		                    marked, notInCourse)
    		        .addOrder(Order.desc(order.getPropertyName()))
    		        .setFirstResult(startIndex)
    		        .setMaxResults(count)
    		        .list();
	}
	
    public long numberOfTutorials(List<String> keywords, 
                                    Long dept, Integer duration, Locale locale, 
                                    List<Status> status, 
                                    Long author, List<Long> nonAuthors,
                                    MarkFilter marked, Long notInCourse) 
    {   
        return(Long) buildSearch(keywords, 
                                    dept, duration, locale, status, 
                                    author, nonAuthors, 
                                    marked, notInCourse)
                        .setProjection(Projections.rowCount())
                        .uniqueResult();
    }
    
    /**
     * Finds all the derived versions of a tutorial, including itself.
     */
    @SuppressWarnings("unchecked")
    public List<Tutorial> findTutorialVersions(long originalId)
    { 
        return(List<Tutorial>)
            getSession().createQuery(
                    "SELECT t FROM Tutorial t " +
            		"WHERE t.tutorialId = :origId OR t.original.tutorialId = :origId")
            		    .setParameter("origId", originalId)
            		    .list();
    }
    
    public boolean existsTranslation(long originalId, Locale locale)
    {
        return getSession().createQuery("SELECT t FROM Tutorial t " +
                                        "WHERE t.original.tutorialId = :origId " +
                                        "  AND t.language = :lang")
                                        .setParameter("origId", originalId)
                                        .setParameter("lang", locale)
                                        .uniqueResult() != null;
    }
	
	@SuppressWarnings("unchecked")
    public List<Tutorial> topTutorials(int count)
	{
	    return(List<Tutorial>) 
	            getSession().createQuery("SELECT t FROM Tutorial t " +
	                                     "ORDER BY t.rating desc")
	                              .setFirstResult(0)
	                              .setMaxResults(count)
	                              .list();
	}
	
/*    public List<TutorialInfo> searchV2(List<String> keywords, Long deptId, Long userId, Time duration, String lang, int startIndex, int count) 
    {
        Criteria c = null;
        Criteria cm = null;
        List <TutorialInfo> lst = new ArrayList<TutorialInfo>();
        //
        
        c = searchTutorials(keywords, deptId, duration, lang);
        c.addOrder(Order.desc("rating"));
        c.setFirstResult(startIndex).setMaxResults(count);
       
        for(Tutorial t : (List<Tutorial>) c.list())
        {
            cm = this.getSession().createCriteria(MarkedTutorial.class);
            cm.add(Restrictions.eq("user.userId", userId));
            cm.add(Restrictions.eq("tutorial.tutorialId", t.getTutorialId()));
            lst.add(new TutorialInfo(t, cm.list()));
        }
        
        
        String TIClass = "com.skillworld.webapp.model.tutorial.TutorialInfo";
        Query q = this.getSession().createQuery("SELECT new "+TIClass+"(t)"
                +" FROM :lt t"
                //+" FROM :lt t, (MarkedTutorial ms LEFT OUTER JOIN m.tutorial ts)"
                //+" WHERE m.user.userId = 1 "
                
                );
        
        return lst;
    }*/
}
